package com.pla.googlecalendarviews;

import java.util.ArrayList;
import java.sql.*;

public class AccountDAO {

  public AccountDAO() {
  }

  public static void main(String[] args) throws Exception {
    AccountDAO dao = new AccountDAO();
    //dao.createTable();
    ArrayList<Account> arrayList = dao.get();
    for (Account account : arrayList) {
      System.out.println(account.getEmailAddress() + " " + account.getAccountId());
    }
  }

  public Account add(String emailAddress, String password) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    Account account = new Account();
    account.setEmailAddress(emailAddress);
    account.setPassword(password);
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("insert into accounts (emailaddress,pwd) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
      statement.setString(1, emailAddress);
      statement.setString(2, password);
      statement.executeUpdate();
      rs = statement.getGeneratedKeys();
      if (rs.next()) {
        account.setAccountId(rs.getInt(1));
        account.setExists(true);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return account;
  }

  public void delete(Account account) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.prepareStatement("delete from accounts where accountid = ?");
      statement.setInt(1, account.getAccountId());
      statement.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(statement, connection);
    }
  }

  public Account update(Account account) {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      if (account.isExists()) {
        statement = connection.prepareStatement("update accounts set emailaddress = ?, pwd = ?  where accountid = ?");
        statement.setString(1, account.getEmailAddress());
        statement.setString(2, account.getPassword());
        statement.setInt(3, account.getAccountId());
        statement.executeUpdate();
      } else {
        statement = connection.prepareStatement("insert into accounts (emailaddress,pwd) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
        statement.setString(1, account.getEmailAddress());
        statement.setString(2, account.getPassword());
        statement.executeUpdate();
        rs = statement.getGeneratedKeys();
        if (rs.next()) {
          account.setAccountId(rs.getInt(1));
          account.setExists(true);
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return account;
  }

  public void createTable() throws Exception {
    Connection connection = null;
    Statement statement = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.createStatement();
      try {
        statement.execute("drop table accounts");
      } catch (SQLException e) {
        e.printStackTrace();
      }
      statement.execute("create table accounts (accountid int generated always as identity, emailaddress varchar(132), pwd varchar(132), primary key(emailaddress))");
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(statement, connection);
    }
  }

  public int replaceAll(ArrayList<Account> accounts) {
    int recordsAdded = 0;
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.createStatement();
      statement.executeUpdate("delete from accounts");
      for (Account account : accounts) {
        if (!Utils.isBlank(account.getEmailAddress())) {
          add(account.getEmailAddress(), account.getPassword());
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return recordsAdded;
  }

  public ArrayList<Account> get() {
    ArrayList<Account> arrayList = new ArrayList<Account>();
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.createStatement();
      rs = statement.executeQuery("Select * from accounts");
      while (rs.next()) {
        Account account = new Account();
        account.setEmailAddress(rs.getString("emailaddress"));
        account.setPassword(rs.getString("pwd"));
        account.setAccountId(rs.getInt("accountid"));
        account.setExists(true);
        arrayList.add(account);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return arrayList;
  }

  public Account get(int accountId) {
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    try {
      connection = Utils.getDbConnection();
      statement = connection.createStatement();
      rs = statement.executeQuery("Select * from accounts where accountid = " + accountId);
      while (rs.next()) {
        Account account = new Account();
        account.setEmailAddress(rs.getString("emailaddress"));
        account.setPassword(rs.getString("pwd"));
        account.setAccountId(rs.getInt("accountid"));
        return account;
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      Utils.close(rs, statement, connection);
    }
    return new Account();
  }
}
